package cn.aopmin.tosql;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;

/**
 *
 */
public class ExcelToInsertSQLGenerator {

    /**
     *  excel 文件路径
     */
    final static String excelFilePath = "C:\\Users\\aopmin\\Desktop\\city不city.xlsx";
    // 数据库表名
    final static String tableName = "your_table_name";
    // 列名数组
    final static String[] tableColumns = {"column1", "column2"};

    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss"; // 定义日期格式
    private static final SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);


    public static void main(String[] args) {
        generateInsertStatements(excelFilePath, tableName, tableColumns);
    }



    public static void generateInsertStatements(String excelFilePath, String tableName, String[] tableColumns) {
        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            StringBuilder insertStatementBase = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
            StringBuilder columns = new StringBuilder();

            // 构建列名部分
            for (int i = 0; i < tableColumns.length; i++) {
                columns.append(tableColumns[i]);
                if (i < tableColumns.length - 1) {
                    columns.append(", ");
                }
            }
            insertStatementBase.append(columns).append(") VALUES (");

            // 遍历每一行
            for (Row row : sheet) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过标题行
                }

                StringBuilder values = new StringBuilder();
                for (int i = 0; i < tableColumns.length; i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        values.append("NULL");
                    } else {
                        switch (cell.getCellType()) {
                            case STRING:
                                values.append("'").append(cell.getStringCellValue().trim()).append("'");
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    values.append("'").append(sdf.format(cell.getDateCellValue())).append("'");
                                } else {
                                    if (cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
                                        values.append((long) cell.getNumericCellValue());
                                    } else {
                                        values.append(cell.getNumericCellValue());
                                    }
                                }
                                break;
                            case BOOLEAN:
                                values.append(cell.getBooleanCellValue());
                                break;
                            default:
                                values.append("NULL");
                        }
                    }

                    if (i < tableColumns.length - 1) {
                        values.append(", ");
                    }
                }

                values.append(");");
                System.out.println(insertStatementBase.toString() + values.toString());
            }

        } catch (IOException e) {
            System.err.println("文件读取失败: " + e.getMessage());
        } catch (IllegalArgumentException e) {
            System.err.println("参数错误: " + e.getMessage());
        } catch (Exception e) {
            System.err.println("未知错误: " + e.getMessage());
        }
    }
    public static void generateInsertStatements2(String excelFilePath, String tableName, String[] tableColumns) {
        // try with resources 写法 , 好处自动关闭流
        try (FileInputStream fis = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            // 遍历每一行
            for (Row row : sheet) {
                if (row.getRowNum() == 0){
                    //  跳过标题行
                    continue;
                }
                // 构建插入语句
                StringBuilder insertStatement = new StringBuilder("INSERT INTO " + tableName + " (");
                StringBuilder values = new StringBuilder("VALUES (");

                for (int i = 0; i < tableColumns.length; i++) {
                    Cell cell = row.getCell(i);
                    // 拼接插入列名
                    insertStatement.append(tableColumns[i]);
                    if (i < tableColumns.length - 1) {
                        insertStatement.append(", ");
                    }
                    // 拼接插入值
                    switch (cell.getCellType()) {
                        case STRING:
                            values.append("'").append(cell.getStringCellValue().trim()).append("'"); // 去掉字符串两边的空格
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                values.append("'").append(cell.getDateCellValue()).append("'"); // 处理日期
                            } else {
                                // 处理数值，根据需要格式化为整数或浮点数
                                if (cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
                                    values.append((long) cell.getNumericCellValue()); // 如果是整数
                                } else {
                                    values.append(cell.getNumericCellValue()); // 如果是浮点数
                                }
                            }
                            break;
                        case BOOLEAN:
                            values.append(cell.getBooleanCellValue());
                            break;
                        default:
                            values.append("NULL");
                    }

                    if (i < tableColumns.length - 1) {
                        values.append(", ");
                    }else {
                        values.append(")");
                    }
                }

                insertStatement.append(") ").append(values).append(";");
                System.out.println(insertStatement.toString());
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}